package com.stuManager.dao;

import com.stuManager.model.Student;
import com.stuManager.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/*
    StudentDao
    处理数据库学生表的相关操作
    增  int createNewStudent(Student student)
    删  int deleteStudent(Student student)
    改  int updateStudent(Student student)
    查  List<Student> fetchStudentList(int page, int size, String searchColumn, String searchValue)
 */
public class StudentDao {
    public List<Student> fetchStudentList(int page, int size, String searchColumn, String searchValue) {
        //1. 获取连接
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<Student> students = new ArrayList<>();
        int start = (page - 1) * size;
        try {
            con = DBUtil.getConnection();
            String sql = "select * from student";
            if (searchColumn.length() > 0
                    && searchValue.length() > 0) {
                sql += " WHERE " + searchColumn + "=?";
            }
            sql += " LIMIT ?,?";
            assert con != null;
            ps = con.prepareStatement(sql);
            if (searchColumn.length() > 0
                    && searchValue.length() > 0) {
                ps.setString(1, searchValue);
                ps.setInt(2, start);
                ps.setInt(3, size);
            }
            else {
                ps.setInt(1, start);
                ps.setInt(2, size);
            }
            resultSet = ps.executeQuery();
            System.out.println("sql is "+sql);
            System.out.println("page "+page + "page size " + size + " column is " + searchColumn + " value is " + searchValue);
            while (resultSet.next()) {
                Student student = new Student();
                student.setStuNumber(resultSet.getString("stuNumber"));
                student.setStuName(resultSet.getString("stuName"));
                student.setBirthday(resultSet.getDate("birthday"));
                student.setEmail(resultSet.getString("email"));
                student.setSex(resultSet.getString("sex"));
                student.setNotes(resultSet.getString("notes"));
                students.add(student);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(con, ps, resultSet);
        }
        return students;
    }

    /*
    带分页查询学生列表
     */
    public List<Student> fetchStudentList(int page, int size) {
        return fetchStudentList(page, size, "", "");
    }
    /*
        带搜索词查询学生列表
     */
    public List<Student> fetchStudentList(String searchColumn, String searchValue){
        return fetchStudentList(1, 10, searchColumn, searchValue);
    }

    //获取学生表的数据总量
    public int fetchStudentCount() {
        //1. 获取连接
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = DBUtil.getConnection();
            String sql = "select COUNT(*) AS count FROM student";
            assert con != null;
            ps = con.prepareStatement(sql);
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(con, ps, resultSet);
        }
        return 0;
    }

    /*
    新创建一条学生的数据
     */
    public int createNewStudent(Student student) {
        //1. 获取连接
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            System.out.println("开始新增");
            con = DBUtil.getConnection();
            String sql = "INSERT INTO student (stuNumber, stuName, sex, birthday, email, notes)"
                    + "VALUES (?, ?, ?, ?, ?, ?)";
            assert con != null;
            java.util.Date utilDate = student.getBirthday();
            java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
            ps = con.prepareStatement(sql);
            ps.setString(1, student.getStuNumber());
            ps.setString(2, student.getStuName());
            ps.setString(3, student.getSex());
            ps.setDate(4,  sqlDate);
            ps.setString(5, student.getEmail());
            ps.setString(6, student.getNotes());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(con, ps, resultSet);
        }
        return 0;
    }

    public int updateStudent(Student student) {
        //1. 获取连接
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            System.out.println("开始更改");
            con = DBUtil.getConnection();
            String sql = "UPDATE student set stuName=?, sex=?, birthday=?, email=?, notes=? WHERE stuNumber=?";
            assert con != null;
            java.util.Date utilDate = student.getBirthday();
            java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
            ps = con.prepareStatement(sql);

            ps.setString(1, student.getStuName());
            ps.setString(2, student.getSex());
            ps.setDate(3,  sqlDate);
            ps.setString(4, student.getEmail());
            ps.setString(5, student.getNotes());
            ps.setString(6, student.getStuNumber());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(con, ps, resultSet);
        }
        return 0;
    }

    public int deleteStudent(Student student) {
        //1. 获取连接
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            System.out.println("开始删除");
            con = DBUtil.getConnection();
            String sql = "DELETE FROM student WHERE stuNumber=?";
            assert con != null;
            ps = con.prepareStatement(sql);
            ps.setString(1, student.getStuNumber());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(con, ps, resultSet);
        }
        return 0;
    }
}
